package com.sf;

import org.slf4j.Logger;

import java.sql.*;
import java.util.*;

public class ClickhouseDemo {

    public static void main(String[] args) {
//        String sqlDB = "show databases";//查询数据库
//        String sqlTab = "show tables";//查看表
//        String sqlCount = "select count(*) count from t_order_mt";//查询ontime数据量
        String sqlInsert = null;
//        exeSql(sqlDB);
//        exeSql(sqlTab);
//        exeSql(sqlCount);


        String address = "jdbc:clickhouse://192.168.147.101:8123/default";
        Connection connection = null;
        Statement statement = null;
        ResultSet results = null;
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
            connection = DriverManager.getConnection(address);
            String sql = "insert into jy_test1_all values(?,?,?,?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql);
//            statement = connection.createStatement();
            String[] param2={"a","b","c","d","e","f","g","h","i","j"};
            String[] param4={"2020-10-21 11:11:11","2020-10-31 11:11:11","2020-09-21 11:11:11","2020-08-21 11:11:11",};
            Double[] param3={100.0,15.0,16.0,17.0,200.0,111.0,56.0,66.0,99.0,22.0,55.1};
            Random r = new Random();
            long begin = System.currentTimeMillis();
            int j = 0;
            for (int i = 0; i <= 1; i++) {
//                ps.setLong(1, Long.parseLong(String.valueOf(i)));
//                ps.setString(2,  param2[r.nextInt(10)]);
//                ps.setString(3,  param2[r.nextInt(10)]);
//                ps.setString(4,  param2[r.nextInt(10)]);
//                ps.setString(5,  param2[r.nextInt(10)]);
//                ps.setDouble(6, param3[r.nextInt(10)]);
//                ps.setDouble(7, param3[r.nextInt(10)]);
//                ps.setString(8, param4[r.nextInt(4)]);
                ps.setLong(1, 1L);
                ps.setString(2, "a1" );
                ps.setString(3,  "a1" );
                ps.setString(4,  "a1" );
                ps.setString(5,  "a1" );
                ps.setDouble(6, 2.0);
                ps.setDouble(7, 2.0);
                ps.setString(8, "2020-10-11 10:10:11");
                ps.addBatch();
                j++;
                if (j % 1 == 0) {
                    int[] ints = ps.executeBatch();
                    connection.commit();
                    ps.clearBatch();
                    System.out.println("影响条数： "+ints[0]);
                }

            }

            ps.executeBatch();
            connection.commit();

            long end = System.currentTimeMillis();
            System.out.println("执行耗时：" + (end - begin) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//关闭连接
            try {
                if (results != null) {
                    results.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /***
     * 查询业务进行打印不输出,测试使用
     * @param sql
     */
    public static void exeSql(String sql) {
        String address = "jdbc:clickhouse://192.168.147.101:8123/default";
        Connection connection = null;
        Statement statement = null;
        ResultSet results = null;
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
            connection = DriverManager.getConnection(address);
            statement = connection.createStatement();
            long begin = System.currentTimeMillis();
            results = statement.executeQuery(sql);
            long end = System.currentTimeMillis();
            System.out.println("执行（" + sql + "）耗时：" + (end - begin) + "ms");
            ResultSetMetaData rsmd = results.getMetaData();
            List<Map> list = new ArrayList();
            while (results.next()) {
                Map map = new HashMap();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    map.put(rsmd.getColumnName(i), results.getString(rsmd.getColumnName(i)));
                }
                list.add(map);
            }
            for (Map map : list) {
                System.err.println(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//关闭连接
            try {
                if (results != null) {
                    results.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /***
     * 查询业务进行打印不输出,测试使用
     * @param sql
     */
    public static void exeSqlWrite(String sql) {
        String address = "jdbc:clickhouse://192.168.197.121:8123/default";
        Connection connection = null;
        Statement statement = null;
        ResultSet results = null;
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
            connection = DriverManager.getConnection(address);
            statement = connection.createStatement();
            long begin = System.currentTimeMillis();
            results = statement.executeQuery(sql);
            long end = System.currentTimeMillis();
            System.out.println("执行（" + sql + "）耗时：" + (end - begin) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//关闭连接
            try {
                if (results != null) {
                    results.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /***
     * clickhouse批量写入
     * @param sql
     */
    public static void exeSqlWriteBatch(String sql) {
        String address = "jdbc:clickhouse://192.168.197.121:8123/default";
        Connection connection = null;
        Statement statement = null;
        ResultSet results = null;
        try {
            Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
            connection = DriverManager.getConnection(address);
            statement = connection.createStatement();
            long begin = System.currentTimeMillis();
            results = statement.executeQuery(sql);
            statement.addBatch("");
            statement.executeBatch();
            long end = System.currentTimeMillis();
            System.out.println("执行（" + sql + "）耗时：" + (end - begin) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//关闭连接
            try {
                if (results != null) {
                    results.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}